DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (key String, attr String, a UInt64, b UInt64, c Nullable(UInt64)) ENGINE = MergeTree ORDER BY key;
INSERT INTO t1 VALUES ('key1', 'a', 1, 1, 2), ('key1', 'b', 2, 3, 2), ('key1', 'c', 3, 2, 1), ('key1', 'd', 4, 7, 2), ('key1', 'e', 5, 5, 5), ('key2', 'a2', 1, 1, 1), ('key4', 'f', 2, 3, 4);
CREATE TABLE t2 (key String, attr String, a UInt64, b UInt64, c Nullable(UInt64)) ENGINE = MergeTree ORDER BY key;
INSERT INTO t2 VALUES ('key1', 'A', 1, 2, 1), ('key1', 'B', 2, 1, 2), ('key1', 'C', 3, 4, 5), ('key1', 'D', 4, 1, 6), ('key3', 'a3', 1, 1, 1), ('key4', 'F', 1,1,1);

SET query_plan_join_swap_table=0; -- result is not deterministic for ANY join
SET enable_analyzer=1;
SET allow_experimental_join_condition=1;
SET join_use_nulls=0;
-- { echoOn }
{% for algorithm in ['hash', 'grace_hash'] -%}
SET join_algorithm='{{ algorithm }}';
{% for join_type in ['LEFT', 'RIGHT'] -%}
{% for join_strictness in ['ANY', 'SEMI', 'ANTI'] -%}
SELECT t1.*, t2.* FROM t1 {{ join_type }} {{ join_strictness }} JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
SELECT t1.*, t2.* from t1 {{ join_type }} {{ join_strictness }}  JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
SELECT t1.*, t2.* from t1 {{ join_type }} {{ join_strictness }}  JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 {{ join_type }} {{ join_strictness }} JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
{% endfor -%}
{% endfor -%}
{% endfor -%}

{% for algorithm in ['hash'] -%}
SET join_algorithm='{{ algorithm }}';
{% for join_type in ['LEFT', 'RIGHT'] -%}
{% for join_strictness in ['ANY', 'SEMI', 'ANTI'] -%}
SELECT t1.* FROM t1 {{ join_type }} {{ join_strictness }} JOIN t2 ON t1.key = t2.key AND t1.a < t2.a OR t1.a = t2.a ORDER BY ALL;
{% endfor -%}
{% endfor -%}
{% endfor -%}

{% for algorithm in ['hash'] -%}
SET join_algorithm='{{ algorithm }}';
{% for join_type in ['LEFT', 'INNER', 'RIGHT', 'FULL', 'SEMI LEFT', 'SEMI RIGHT'] -%}
SELECT t1.*, t2.* FROM t1 {{ join_type }} JOIN t2 ON t1.key = t2.key AND t1.a < t2.a OR t1.a = t2.a ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
{% endfor -%}
{% endfor -%}

{% for algorithm in ['hash', 'grace_hash', 'parallel_hash'] -%}
SET join_algorithm='{{ algorithm }}';
{% for join_type in ['INNER'] -%}
{% for join_strictness in ['ANY'] -%}
SELECT t1.*, t2.* FROM t1 {{ join_type }} {{ join_strictness }} JOIN t2 ON (t1.a < t2.a OR lower(t1.attr) == lower(t2.attr)) AND t1.key = t2.key ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
SELECT t1.*, t2.* from t1 {{ join_type }} {{ join_strictness }}  JOIN t2 ON t1.key = t2.key and (t1.b + t2.b == t1.c + t2.c) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
SELECT t1.*, t2.* from t1 {{ join_type }} {{ join_strictness }}  JOIN t2 ON t1.key = t2.key and (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);
SELECT * FROM (SELECT 1 AS a, 1 AS b, 1 AS c) AS t1 {{ join_type }} {{ join_strictness }} JOIN (SELECT 1 AS a, 1 AS b, 1 AS c) AS t2 ON t1.a = t2.a AND (t1.b > 0 OR t2.b > 0);
{% endfor -%}
{% endfor -%}
{% endfor -%}

{% for algorithm in ['hash'] -%}
SET join_algorithm='{{ algorithm }}';
{% for join_type in ['INNER'] -%}
{% for join_strictness in ['ANY'] -%}
SELECT t1.* FROM t1 {{ join_type }} {{ join_strictness }} JOIN t2 ON t1.key = t2.key AND t1.a < t2.a OR t1.a = t2.a ORDER BY ALL;
{% endfor -%}
{% endfor -%}
{% endfor -%}

-- { echoOff }

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
